﻿CREATE PROCEDURE [dbo].[usp_select_AlertEmail]
(
	@BatchSize	int
   ,@Debug      bit             = 0
)
AS

SET NOCOUNT ON;

-- Bootstrap
DECLARE @DebugPrefix       varchar(10)
DECLARE @ErrorCode         int

SET @ErrorCode          = 0;
SET @DebugPrefix        = '>>> DEBUG: '

-- Print parameters
IF (@Debug > 0)
BEGIN
   PRINT @DebugPrefix + '@BatchSize =' + CONVERT(varchar, @BatchSize)
END;
DECLARE @Statement nvarchar(4000)
SET @Statement = N'SELECT TOP ' + CONVERT(varchar, @BatchSize) + '
   ae.[AlertEmailID]
  ,ae.[AlertSubscriptionHistoryID]
  ,ae.[To]
  ,ae.[Subject]
  ,ae.[Body]
  ,ae.[Sent]
  ,ae.[BatchID]
  ,ash.[Subscriber]
  ,ash.[IsGroup]
  ,ash.[CustomEmail]
	
FROM dbo.AlertEmail ae 
INNER JOIN dbo.AlertSubscriptionHistory ash ON ash.AlertSubscriptionHistoryID = ae.AlertSubscriptionHistoryID
INNER JOIN dbo.Batch b ON b.BatchID = ae.BatchID
WHERE 
	ae.Sent = 0
	AND b.EndDatetime IS NOT NULL;'

EXECUTE sp_executesql @Statement

-- CHECK FOR ERROR
SET @ErrorCode = @@Error;
IF (@ErrorCode <> 0) GOTO ERROR_HANDLER;
			
RETURN 0;

-- Handles errors.
ERROR_HANDLER:
IF (@ErrorCode <> 0)
BEGIN
    IF (@Debug > 0) 
		BEGIN
			PRINT @DebugPrefix + 'Error Code = ' + CAST(@ErrorCode AS varchar(10)) + ' occurred.';
			RETURN @ErrorCode
		END

    -- Don't return any evidence of what specific error occurred, i.e. 
    -- we want to ward off potential hackers.
    RETURN 1;
END;

SET NOCOUNT OFF;
